package com.wl.dzt.sys.core.dao.curd.mssql;


import com.wl.dzt.sys.core.dao.curd.CrudCommand;
import com.wl.dzt.sys.core.dao.curd.CrudConvertResult;
import com.wl.dzt.sys.core.dao.curd.mysql.CrudCommandForMysql;
import com.wl.dzt.sys.core.dao.curd.sql.SqlSearchResult;
import com.wl.dzt.sys.core.dao.curd.sql.el.SqlParam;
import com.wl.dzt.sys.core.dao.curd.sql.el.SqlParamBuilder;
import com.wl.dzt.sys.core.dao.select.Select;
import com.wl.dzt.sys.util.LogicUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.*;


/**
 * @author wl_code@163.com
 * @version 1.0
 * @date 2018/7/5/005
 * @description microsoft sql server增删改查命令
 */
@Component("microsoft sql server")
public class CrudCommandForMssql extends CrudCommandForMysql implements CrudCommand {

    private static final Logger log = LoggerFactory.getLogger(CrudCommandForMssql.class);

    /**
     * mysql转换器
     */
    @Resource
    private CrudConvertForMssql crudConvertForMssql;

    /**
     * 根据查询条件查询数据
     *
     * @param select
     * @return
     */
    public List<?> select(Select select) {
        return getObjects(select, crudConvertForMssql);
    }

    /**
     * <b>不适配实体查询</b>
     *
     * @param select
     * @return
     */
    public List<Map<String, Object>> selectByMap(Select select) {
        return getMaps(select, crudConvertForMssql, log);
    }

    /**
     * 根据查询条件查询总数
     *
     * @param select
     * @return
     */
    public Integer count(Select select) {
        return countMethod(select, crudConvertForMssql);
    }

    /**
     * 根据特殊的sql表达式子查询数据
     *
     * @param select
     * @return
     */
    public SqlSearchResult selectBySqlExp(Select select) {
        SqlSearchResult sqlSearchResult = new SqlSearchResult();
        String old = select.getSqlExp().trim().replaceAll(" +", " ");
        String expSql = new StringBuffer(old).insert(6, " row_number () OVER (ORDER BY  getdate() ) as tempPageIndexRowNumber, ")
                .toString();
        String orderBy = "";
        try {
            int order = old.indexOf("ORDER BY");
            if (order == -1) {
                order = old.indexOf("order by");
            }
            if (order != -1) {
                orderBy = old.substring(order, old.length());
                expSql = expSql.replace(orderBy, "");
            }
            select.setSqlExp(expSql);
            int s = select.getStart() + 1, l = select.getLimit()
                    * (select.getStart() != 0 ? (select.getStart() / select.getLimit() + 1) : 1);
            // 查询数据
            SqlParamBuilder sqlParamBuilder = new SqlParamBuilder(select.getSqlExp());
            SqlParam sqlParam = sqlParamBuilder.create(select.getWheres(), select.getStart(), select.getLimit());
            String sql = (select.getLimit() == 0 && select.getStart() == 0) ? sqlParam.getFullSql() : String.format(
                    crudConvertForMssql.getPageTemplate(), sqlParam.getFullSql(), s, l) + orderBy;
            List<Map<String, Object>> data = jdbcTemplate.queryForList(sql), list = new ArrayList<>();
            log.debug("query:{}", sql);
            data.forEach(row -> {
                Map<String, Object> map = new HashMap<>();
                row.forEach((k, v) -> map.put(k.replaceAll("_", "."), v));
                list.add(map);
            });
            sqlSearchResult.setData(data);
            // 查询总数
            Map<String, Object> result = jdbcTemplate.queryForMap(sqlParam.countSql());
            log.debug("query:{}", sqlParam.countSql());
            if (result != null) {
                Object longStr = LogicUtil.empty(result.get("count")) ? result.get("COUNT") : result.get("count");
                Integer count = new Integer(longStr.toString());
                sqlSearchResult.setCount(count);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlSearchResult;
    }
}
